from sqlalchemy import func
from sqlalchemy.exc import SQLAlchemyError
import os,sys
sys.path.append((os.path.dirname(os.path.abspath(__file__))))
from model import SuperManager,Manager,User,CommentOnFilm,MarkCommend,Recomment,ULoveF,FilmInfor,get_love_film
from model import db
from sqlalchemy import desc,asc
from flask import jsonify

class E_SuperManager:

    @staticmethod
    def get_supermanager():
        return SuperManager.query.all()#返回一个SuperManager对象的列表
    
    @staticmethod
    def get_super_manager_by_id(super_ma_id):
        super_manager = SuperManager.query.get(super_ma_id)
        if super_manager:
            super_manager_dict = {
                'super_ma_id': super_manager.super_ma_id,
                'super_password': super_manager.super_password,
                'all_film_num': super_manager.all_film_num,
                'all_user_num': super_manager.all_user_num,
                'all_manager_num': super_manager.all_manager_num
            }
            return super_manager_dict
        else:
            return None

class E_Manager:
    
    @staticmethod
    def vetify_email(manager_id,manager_email):
        # Check if id or email already exists
        existing_manager_by_id = Manager.query.filter_by(manager_id=manager_id).first()
        existing_manager_by_email = Manager.query.filter_by(manager_email=manager_email).first()

        if existing_manager_by_id:
            return None
        if existing_manager_by_email:
            return None
        return "done"
    
    @staticmethod
    def add_manager(manager_id,manager_password, manager_name, manager_email):
        new_manager = Manager(
            manager_id=manager_id,
            manager_password=manager_password,
            manager_name=manager_name,
            manager_email=manager_email
        )
        db.session.add(new_manager)
        try:
            db.session.commit()
            super_manager = SuperManager.get_super_manager()
            super_manager.all_manager_num += 1
            db.session.commit()
            return "done"
        except SQLAlchemyError as e:
            db.session.rollback()
            return None

    @staticmethod
    def get_manager_by_id(manager_id):
        manager = Manager.query.get(manager_id)
        if manager:
            manager_dict = {
                'manager_id': manager.manager_id,
                'manager_password': manager.manager_password,
                'manager_name': manager.manager_name,
                'manager_email': manager.manager_email
            }
            return manager_dict
        else:
            return None
    
    #返回所有manager对象
    @staticmethod
    def get_manager():
        managers = Manager.query.all()
        return [manager.to_dict() for manager in managers]

    @staticmethod
    def update_manager(manager_id, **kwargs):#**kwargs: 关键字参数，用于传递需要更新的字段及其新值。它是一个可变数量的参数，能够接收字典形式的键值对。
        manager = Manager.query.get(manager_id)
        if manager:
            for key, value in kwargs.items():
                setattr(manager, key, value)#使用 setattr() 函数动态地将 key 属性设置为 value。这使得我们可以灵活地更新多个属性，而不需要硬编码每个属性的更新。
            try:
                db.session.commit()
                return manager
            except SQLAlchemyError as e:
                db.session.rollback()
                return None
        return None

    @staticmethod
    def delete_manager(manager_id):
        manager = Manager.query.get(manager_id)
        if manager:
            db.session.delete(manager)
            try:
                db.session.commit()
                super_manager = SuperManager.get_super_manager()
                super_manager.all_manager_num -= 1
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
                return False
        return False

class E_User:
    @staticmethod
    def vetify_email(user_id,user_email):
        # Check if user_id or user_email already exists
        existing_user_by_id = User.query.filter_by(user_id=user_id).first()
        existing_user_by_email = User.query.filter_by(user_email=user_email).first()

        if existing_user_by_id:
            return None
        if existing_user_by_email:
            return None
        return "done"
    
    
    @staticmethod
    def add_user(user_id,user_password, user_name, user_email, ban_not=False, ban_reason=None, ban_ma_id=None, block_not=False, block_reason=None, block_ma_id=None, complain_not=False):
        
        new_user = User(
            user_id=user_id,
            user_password=user_password,
            user_name=user_name,
            ban_not=ban_not,
            user_email=user_email,
            ban_reason=ban_reason,
            ban_ma_id=ban_ma_id,
            block_not=block_not,
            block_reason=block_reason,
            block_ma_id=block_ma_id,
            complain_not=complain_not
        )
        db.session.add(new_user)
        try:
            db.session.commit()
            super_manager = SuperManager.get_super_manager()
            super_manager.all_user_num += 1
            db.session.commit()
            return "done"
        except SQLAlchemyError as e:
            db.session.rollback()

            return None


    @staticmethod
    def get_user_by_id(user_id):
        user = User.query.get(user_id)
        if user:
            user_dict = {
                'user_id': user.user_id,
                'user_password': user.user_password,
                'user_name': user.user_name,
                'ban_not': user.ban_not,
                'ban_reason': user.ban_reason,
                'ban_ma_id': user.ban_ma_id,
                'block_not': user.block_not,
                'block_reason': user.block_reason,
                'block_ma_id': user.block_ma_id,
                'complain_not': user.complain_not,
                'user_email': user.user_email,
                'complain_reason':user.complain_reason
            }
            return user_dict
        else:
            return None

    @staticmethod
    def get_user():
        users = User.query.all()
        return [user.to_dict() for user in users]
    
    @staticmethod
    def get_user_complain(manager_id):
        users = User.query.filter(
            ((User.ban_ma_id == manager_id) & (User.ban_not == True)) | 
            ((User.block_ma_id == manager_id) & (User.block_not == True))
        ).filter(User.complain_not == True).all()
        
        return [user.to_dict() for user in users]
    
    @staticmethod
    def user_complain(user_id,complain):
        user =  User.query.get(user_id)
        if user:
            user.complain_not = 1
            user.complain_reason = complain
            try:
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
                return None
            
        return None
    
    @staticmethod
    def update_user(user_id, **kwargs):
        user = User.query.get(user_id)
        if user:
            for key, value in kwargs.items():
                setattr(user, key, value)
            try:
                db.session.commit()
                return user
            except SQLAlchemyError as e:
                db.session.rollback()
                
                return None
        return None

    @staticmethod
    def delete_user(user_id):
        user = User.query.get(user_id)
        if user:
            db.session.delete(user)
            try:
                db.session.commit()
                super_manager = SuperManager.get_super_manager()
                super_manager.all_user_num -= 1
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
              
                return False
        return False

class E_FilmInfor:
    #超管完成的操作
    @staticmethod
    def add_film(film_name, actor, director, release_time, introductory,www_http ,film_mark=0, comment_num=0, long_comment_num=0, love_num=0, assist_num=0, watch_num=0):
        max_film_id = db.session.query(func.max(FilmInfor.film_id)).scalar()
        if max_film_id is None:
            max_film_id = 1
        else:
            new_film_id = max_film_id + 1
        new_film = FilmInfor(
            film_id=new_film_id,
            film_name=film_name,
            actor=actor,
            director=director,
            release_time=release_time,
            film_mark=film_mark,
            introductory=introductory,
            comment_num=comment_num,
            long_comment_num=long_comment_num,
            love_num=love_num,
            assist_num=assist_num,
            watch_num=watch_num,
            www_http=www_http
        )
        db.session.add(new_film)
        try:
            db.session.commit()
            super_manager = SuperManager.get_super_manager()
            super_manager.all_film_num += 1
            db.session.commit()
            return True
        except SQLAlchemyError as e:
            db.session.rollback()
            
            return None
    
    #评分后更新电影平均评分
    @staticmethod
    def update_film_mark(mark_id):
        # 根据mark_id找到相应的评分记录
        mark_commend = MarkCommend.query.get(mark_id)
        if not mark_commend:
            return None
        
        # 获取相应的film_id
        film_id = mark_commend.film_id
        

        # 更新电影信息中的评分总数和评分数量
        film = FilmInfor.query.get(film_id)
        if film:
            film.mark_sum += mark_commend.mark
            film.mark_num += 1
            film.film_mark = film.mark_sum/film.mark_num

            # 提交数据库更新
            db.session.commit()
            return True
        return None
        
        
    @staticmethod
    def get_film_by_id(film_id):
        films = FilmInfor.query.get(film_id)
        return films.to_dict() 
    
    @staticmethod
    def get_film_by_name(film_name):
        return FilmInfor.query.get(film_name)
    
    #搜索功能
    @staticmethod
    def search_films(keyword):
        keyword = f"%{keyword}%"
        films = FilmInfor.query.filter(
            (FilmInfor.film_name.like(keyword)) |
            (FilmInfor.actor.like(keyword)) |
            (FilmInfor.director.like(keyword))
        ).order_by(FilmInfor.film_mark.desc()).limit(50).all()
        
        if films :
            return [film.to_dict() for film in films]
        return None
    
    #超管完成的操作
    @staticmethod
    def update_film(film_id, **kwargs):
        film = FilmInfor.query.get(film_id)
        if film:
            for key, value in kwargs.items():
                setattr(film, key, value)
            try:
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
                
                return None
        return None

    
    #超管完成的操作
    @staticmethod
    def delete_film(film_id):
        film = FilmInfor.query.get(film_id)
        if film:
            db.session.delete(film)
            try:
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
                
                return False
        return False
    
    #推荐电影
    @staticmethod
    def assist_film(film_id):
        film = FilmInfor.query.get(film_id)
        if film:
            film.assist_num+=1
            try:
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
                return None
        return  None
    
    #电影状态更新
    @staticmethod
    def update_film_state(film_id, comment_num=0, long_comment_num=0, love_num=0, assist_num=0, watch_num=0):
        film = FilmInfor.query.get(film_id)
        if not film:
            return None

        film.comment_num += comment_num
        film.long_comment_num += long_comment_num
        film.love_num += love_num
        film.assist_num += assist_num
        film.watch_num += watch_num

        try:
            db.session.commit()
            return True
        except SQLAlchemyError as e:
            db.session.rollback()
            return None
    
    #返回电影主界面数据
    @staticmethod
    def get_film_info():
        # Fetch the latest 50 films by release_time
        latest_films = FilmInfor.query.order_by(desc(FilmInfor.release_time),asc(FilmInfor.film_id)).limit(50).all()

        # Fetch the top 50 highest-rated films, if scores are equal, order by release_time
        highest_rated_films = FilmInfor.query.order_by(desc(FilmInfor.film_mark),asc(FilmInfor.film_id)).limit(50).all()

        # Fetch the top 50 most loved films by love_num
        most_loved_films = FilmInfor.query.order_by(desc(FilmInfor.love_num),asc(FilmInfor.film_id)).limit(50).all()

        # Convert the query results to a list of dictionaries
        latest_films_list = [film.to_dict() for film in latest_films]
        highest_rated_films_list = [film.to_dict() for film in highest_rated_films]
        most_loved_films_list = [film.to_dict() for film in most_loved_films]

        # Return the results in JSON format
        return jsonify({
            'latest_films': latest_films_list,
            'highest_rated_films': highest_rated_films_list,
            'most_loved_films': most_loved_films_list
        })
    
    #返回更多电影 
    @staticmethod
    def get_more_films(film_type, last_film_id):
        query = None
        if film_type == 'release_time':
            last_film = FilmInfor.query.filter_by(film_id=last_film_id).first()
            if last_film:
                query = FilmInfor.query.filter(FilmInfor.release_time <= last_film.release_time, FilmInfor.film_id > last_film_id).order_by(desc(FilmInfor.release_time), asc(FilmInfor.film_id)).limit(50)
        elif film_type == 'film_mark':
            last_film = FilmInfor.query.filter_by(film_id=last_film_id).first()
            if last_film:
                query = FilmInfor.query.filter(FilmInfor.film_mark <= last_film.film_mark, FilmInfor.film_id > last_film_id).order_by(desc(FilmInfor.film_mark), asc(FilmInfor.film_id)).limit(50)
        elif film_type == 'love_num':
            last_film = FilmInfor.query.filter_by(film_id=last_film_id).first()
            if last_film:
                query = FilmInfor.query.filter(FilmInfor.love_num <= last_film.love_num, FilmInfor.film_id > last_film_id).order_by(desc(FilmInfor.love_num), asc(FilmInfor.film_id)).limit(50)

        if query:
            films = query.all()
            films_list = [film.to_dict() for film in films]
            return films_list
        else:
            return None
    
class E_CommentOnFilm:
    
    @staticmethod
    def add_comment(film_id, user_id, long_comment, comment_date, comment_assist=0, top_not=0, comment_pass=0):
        max_comment_id = db.session.query(func.max(CommentOnFilm.film_comment_id)).scalar()
        if max_comment_id is None:
            max_comment_id= 1
        else:
            max_comment_id = max_comment_id + 1
        new_comment = CommentOnFilm(
            film_comment_id=max_comment_id,
            film_id=film_id,
            user_id=user_id,
            long_comment=long_comment,
            comment_date=comment_date,
            comment_assist=comment_assist,
            top_not=top_not,
            comment_pass=comment_pass
        )
        db.session.add(new_comment)
        try:
            db.session.commit()
            return 1
        except SQLAlchemyError as e:
            db.session.rollback()
            
            return None

    @staticmethod
    def get_comment_by_id(film_comment_id):
        return CommentOnFilm.query.get(film_comment_id)
    
    # 返回所有 comment_pass=0 的评论，按照时间先后排序
    @staticmethod
    def get_comment():
        comments = CommentOnFilm.query.filter_by(comment_pass=False).order_by(CommentOnFilm.comment_date).all()
        return [comment.to_dict() for comment in comments]
    
    
    #删除指定评论
    @staticmethod
    def delete_comment(film_comment_id):
        comment = CommentOnFilm.query.get(film_comment_id)
        if comment:
            db.session.delete(comment)
            try:
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
                
                return False
        return False
    
    #评论状态更新
    @staticmethod
    def update_comment_state(film_comment_id, comment_assist=0, top_not=0, comment_pass=0):
        comment = CommentOnFilm.query.get(film_comment_id)
        if not comment:
            return None

        comment.comment_assist += comment_assist
        comment.top_not = top_not
        comment.comment_pass = comment_pass

        try:
            db.session.commit()
            return comment
        except SQLAlchemyError as e:
            db.session.rollback()
            return None
     
    #得到长评
    @staticmethod
    def get_commentonfilm_by_film_id(film_id, last_assist=None, last_id=None):
        query = CommentOnFilm.query.filter(CommentOnFilm.film_id == film_id,
            CommentOnFilm.comment_pass == True)#找到对应的电影id

        if last_assist is not None and last_id is not None:
            query = query.filter(
                db.or_(
                    CommentOnFilm.comment_assist < last_assist,
                    db.and_(
                        CommentOnFilm.comment_assist == last_assist,
                        CommentOnFilm.film_comment_id > last_id
                    )
                )
            )#当需要增加评论时使用

        comments = query.order_by(
            desc(CommentOnFilm.comment_assist),
            CommentOnFilm.film_comment_id#排序
        ).limit(51).all()

        has_more = len(comments) > 50#判断评论数是否有剩
        if has_more:
            comments = comments[:-1]

        return [comment.to_dict() for comment in comments], has_more
   
class E_MarkCommend:

    @staticmethod
    def add_mark(film_id, user_id, mark, short_comment, short_assist=0, short_top_not=False):
        # 找到当前最大 mark_id
        max_mark_id = db.session.query(func.max(MarkCommend.mark_id)).scalar()
        # 如果数据库中没有任何记录，设置初始值为1
        new_mark_id = None
        if max_mark_id is None:
            new_mark_id = 1
        else:
            new_mark_id = max_mark_id + 1
            
        new_mark = MarkCommend(
            mark_id=new_mark_id,
            film_id=film_id,
            user_id=user_id,
            mark=mark,
            short_comment=short_comment,
            short_assist=short_assist,
            short_top_not=short_top_not
        )
        db.session.add(new_mark)
        E_FilmInfor.update_film_mark(new_mark.mark_id)
        try:
            db.session.commit()
            return 1
        except SQLAlchemyError as e:
            db.session.rollback()
            return None

    @staticmethod
    def get_mark_by_id(mark_id):
        return MarkCommend.query.get(mark_id)
    
    #删除指定短评
    @staticmethod
    def delete_mark(mark_id):
        mark = MarkCommend.query.get(mark_id)
        if mark:
            
            Recomment.query.filter_by(mark_id=mark_id).delete()
            db.session.delete(mark)
            try:
                db.session.commit()
                film = FilmInfor.query.filter_by(film_id=mark.film_id)
                if film:
                    film.mark_sum -= mark.mark
                    film.mark_num -=1
                    film.film_mark= film.mark_sum/film.mark_num
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
                return False
        return False
    
    #短评状态更新
    @staticmethod
    def update_mark_state(mark_id, short_assist=0, short_top_not=False):
        mark = MarkCommend.query.get(mark_id)
        if not mark:
            return None

        mark.short_assist += short_assist
        mark.short_top_not = short_top_not

        try:
            db.session.commit()
            return mark
        except SQLAlchemyError as e:
            db.session.rollback()
            return None
    
    #获取短评
    @staticmethod
    def get_markcommend_by_film_id(film_id, last_assist=None, last_id=None):
        query = MarkCommend.query.filter(MarkCommend.film_id == film_id)

        if last_assist is not None and last_id is not None:
            query = query.filter(
                db.or_(
                    MarkCommend.short_assist < last_assist,
                    db.and_(
                        MarkCommend.short_assist == last_assist,
                        MarkCommend.mark_id > last_id
                    )
                )
            )

        marks = query.order_by(
            desc(MarkCommend.short_assist),
            MarkCommend.mark_id
        ).limit(51).all()

        has_more = len(marks) > 50
        if has_more:
            marks = marks[:-1]

        return [mark.to_dict() for mark in marks], has_more
    
class E_Recomment:

    @staticmethod
    def add_recomment(mark_id, user_id, recomment, re_assist=0, re_top_not=False):
        # 找到当前最大 recomment_id
        max_recomment_id = db.session.query(func.max(Recomment.recomment_id)).scalar()
        # 如果数据库中没有任何记录，设置初始值为1
        new_recomment_id = None
        if max_recomment_id is None:
            new_recomment_id = 1
        else:
            new_recomment_id = max_recomment_id + 1
        new_recomment = Recomment(
            recomment_id=new_recomment_id,
            mark_id=mark_id,
            user_id=user_id,
            recomment=recomment,
            re_assist=re_assist,
            re_top_not=re_top_not
        )
        db.session.add(new_recomment)
        try:
            db.session.commit()
            return 1
        except SQLAlchemyError as e:
            db.session.rollback()
            
            return None

    @staticmethod
    def get_recomment_by_id(recomment_id):
        return Recomment.query.get(recomment_id)


    #删除指定回复
    @staticmethod
    def delete_recomment(recomment_id):
        recomment = Recomment.query.get(recomment_id)
        if recomment:
            db.session.delete(recomment)
            try:
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
                
                return False
        return False
    
    #回评状态更新
    @staticmethod
    def update_recomment_state(recomment_id, re_assist=0, re_top_not=False):
        recomment = Recomment.query.get(recomment_id)
        if not recomment:
            return None

        recomment.re_assist+=re_assist
        recomment.re_top_not=re_top_not

        try:
            db.session.commit()
            return recomment
        except SQLAlchemyError as e:
            db.session.rollback()
            return None
    
    #获取回评
    @staticmethod
    def get_recomment_by_markcomment_id(mark_id, last_assist=None, last_id=None):
        query = Recomment.query.filter(Recomment.mark_id == mark_id)

        if last_assist is not None and last_id is not None:
            query = query.filter(
                db.or_(
                    Recomment.re_assist < last_assist,
                    db.and_(
                        Recomment.re_assist == last_assist,
                        Recomment.recomment_id > last_id
                    )
                )
            )

        recomments = query.order_by(
            desc(Recomment.re_assist),
            Recomment.recomment_id
        ).limit(51).all()

        has_more = len(recomments) > 50
        if has_more:
            recomments = recomments[:-1]

        return [recomment.to_dict() for recomment in recomments], has_more

class E_ULoveF:

    @staticmethod
    def add_u_love_f(user_id, film_id):
        new_u_love_f = ULoveF(
            user_id=user_id,
            film_id=film_id
        )
        db.session.add(new_u_love_f)
        try:
            db.session.commit()
            return 1
        except SQLAlchemyError as e:
            db.session.rollback()
            
            return None
    
    
    #获取关注列表
    @staticmethod
    def get_u_love_f(user_id):
        return get_love_film(user_id)
    
    @staticmethod
    def delete_u_love_f(user_id, film_id):
        u_love_f = ULoveF.query.get((user_id, film_id))
        if u_love_f:
            db.session.delete(u_love_f)
            try:
                db.session.commit()
                return True
            except SQLAlchemyError as e:
                db.session.rollback()
                return False
        return False
    
    
